using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Text;
using BlogEngine.Core;

namespace BlogEngine.Core.Providers
{
	/// <summary>
	/// Microsoft SQL Server Implementation of BlogProvider
	/// </summary>
	public partial class MSSQLBlogProvider : BlogProviderBase
	{
		/// <summary>
		/// Retrieves a post based on the specified Id.
		/// </summary>
		public override Post SelectPost(Guid id)
		{
			Post post = new Post();

			SQLManagedConnection myConn = new SQLManagedConnection(_DatabaseName);

			//get the main post details
			try
			{
				myConn.GetTextCommand("SELECT PostID, Title, Description, PostContent, DateCreated, " +
							"DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug " +
							"FROM be_Posts " +
							"WHERE PostID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader rdr = myConn.ExecuteReader())
				{
					rdr.Read();

					post.Id = rdr.GetGuid(0);
					post.Title = rdr.GetString(1);
					//post.Content = rdr.GetString(3);
					if (!rdr.IsDBNull(2))
						post.Description = rdr.GetString(2);
					if (!rdr.IsDBNull(4))
						post.DateCreated = rdr.GetDateTime(4);
					if (!rdr.IsDBNull(5))
						post.DateModified = rdr.GetDateTime(5);
					if (!rdr.IsDBNull(6))
						post.Author = rdr.GetString(6);
					if (!rdr.IsDBNull(7))
						post.IsPublished = rdr.GetBoolean(7);
					if (!rdr.IsDBNull(8))
						post.IsCommentsEnabled = rdr.GetBoolean(8);
					if (!rdr.IsDBNull(9))
						post.Raters = rdr.GetInt32(9);
					if (!rdr.IsDBNull(10))
						post.Rating = rdr.GetFloat(10);
					if (!rdr.IsDBNull(11))
						post.Slug = rdr.GetString(11);

					rdr.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			// Tags
			try
			{
				myConn.GetTextCommand("SELECT Tag FROM be_PostTag WHERE PostID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader rdr = myConn.ExecuteReader())
				{
					while (rdr.Read())
					{
						if (!rdr.IsDBNull(0))
							post.Tags.Add(rdr.GetString(0));
					}

					rdr.Close();
					post.Tags.MarkOld();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			// Categories
			try
			{
				myConn.GetTextCommand("SELECT CategoryID FROM be_PostCategory WHERE PostID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader rdr = myConn.ExecuteReader())
				{
					while (rdr.Read())
					{
						Guid key = rdr.GetGuid(0);
						if (Category.GetById(key) != null)
						{
							post.Categories.Add(Category.GetById(key));
						}
					}

					rdr.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			// Comments
			try
			{
				myConn.GetTextCommand("SELECT PostCommentID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved " +
									  "FROM be_PostComment " +
									  "WHERE PostID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader rdr = myConn.ExecuteReader())
				{
					while (rdr.Read())
					{
						Comment comment = new Comment();
						comment.Id = rdr.GetGuid(0);
						comment.IsApproved = true;
						comment.Author = rdr.GetString(2);
						if (!rdr.IsDBNull(4))
						{
							Uri website;
							if (Uri.TryCreate(rdr.GetString(4), UriKind.Absolute, out website))
								comment.Website = website;
						}
						comment.Email = rdr.GetString(3);
						comment.Content = rdr.GetString(5);
						comment.DateCreated = rdr.GetDateTime(1);
						comment.Parent = post;

						if (!rdr.IsDBNull(6))
							comment.Country = rdr.GetString(6);
						if (!rdr.IsDBNull(7))
							comment.IP = rdr.GetString(7);
						if (!rdr.IsDBNull(8))
							comment.IsApproved = rdr.GetBoolean(8);
						else
							comment.IsApproved = true;

						post.Comments.Add(comment);
					}

					rdr.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			post.Comments.Sort();

			// Email Notification
			try
			{
				myConn.GetTextCommand("SELECT NotifyAddress FROM be_PostNotify WHERE PostID = @id");
				myConn.AddInParameter("@id", DbType.Guid, id);

				using (IDataReader rdr = myConn.ExecuteReader())
				{
					while (rdr.Read())
					{
						if (!rdr.IsDBNull(0))
							post.NotificationEmails.Add(rdr.GetString(0));
					}

					rdr.Close();
				}
			}
			finally
			{
				myConn.CloseIfNonTransactional();
			}

			return post;
		}


        /// <summary>
        /// Retrieves the content of the post in order to lazy load.
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public override string SelectPostContent(Guid id)
        {
            SQLManagedConnection myConn = new SQLManagedConnection(_DatabaseName);
            myConn.GetTextCommand("SELECT PostContent FROM be_Posts WHERE PostID = @id");
            myConn.AddInParameter("@id", DbType.Guid, id);
            return myConn.ExecuteScalar() as string;
        }

		/// <summary>
		/// Inserts a new Post to the data store.
		/// </summary>
		public override void InsertPost(Post post)
		{
			if (post == null)
			{
				throw new ArgumentNullException("post");
			}

			SQLManagedConnection myConn = SQLManagedConnection.GetConnectionForTransaction(_DatabaseName);

			try
			{
				myConn.GetTextCommand("INSERT INTO be_Posts (" +
							"PostID, Title, Description, PostContent, DateCreated, " +
							"DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug)" +
							"VALUES (@id, @title, @desc, @content, @created, @modified, " +
							"@author, @published, @commentEnabled, @raters, @rating, @slug)");
				myConn.AddInParameter("@id", DbType.Guid, post.Id);
				myConn.AddInParameter("@title", DbType.String, post.Title);
				myConn.AddInParameter("@desc", DbType.String, post.Description);
				myConn.AddInParameter("@content", DbType.String, post.Content);
				myConn.AddInParameter("@created", DbType.DateTime, post.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@modified", DbType.DateTime, post.DateModified.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@author", DbType.String, post.Author);
				myConn.AddInParameter("@published", DbType.Boolean, post.IsPublished);
				myConn.AddInParameter("@commentEnabled", DbType.Boolean, post.IsCommentsEnabled);
				myConn.AddInParameter("@raters", DbType.Int32, post.Raters);
				myConn.AddInParameter("@rating", DbType.Single, post.Rating);
				myConn.AddInParameter("@slug", DbType.String, post.Slug);

				myConn.ExecuteNonQuery();

				//Update associated tables
				UpdateTags(post, myConn);
				UpdateCategories(post, myConn);
				UpdateComments(post, myConn);
				UpdateNotify(post, myConn);

				myConn.Commit();

				Post.Items.Add(post);
				Post.Items.Sort();
			}
			finally
			{
				//if commit worked then this will do nothing
				myConn.Rollback();
			}

		}

		/// <summary>
		/// Updates a Post.
		/// </summary>
		public override void UpdatePost(Post post)
		{
			if (post == null)
				throw new ArgumentNullException("post");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnectionForTransaction(_DatabaseName);

			try
			{
				myConn.GetTextCommand("UPDATE be_Posts " +
							"SET Title = @title, Description = @desc, PostContent = @content, " +
							"DateCreated = @created, DateModified = @modified, Author = @Author, " +
							"IsPublished = @published, IsCommentEnabled = @commentEnabled, " +
							"Raters = @raters, Rating = @rating, Slug = @slug " +
							"WHERE PostID = @id");
				myConn.AddInParameter("@id", DbType.Guid, post.Id);
				myConn.AddInParameter("@title", DbType.String, post.Title);
				myConn.AddInParameter("@desc", DbType.String, post.Description);
				myConn.AddInParameter("@content", DbType.String, post.Content);
				myConn.AddInParameter("@created", DbType.DateTime, post.DateCreated.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@modified", DbType.DateTime, post.DateModified.AddHours(-BlogSettings.Instance.Timezone));
				myConn.AddInParameter("@author", DbType.String, post.Author);
				myConn.AddInParameter("@published", DbType.Boolean, post.IsPublished);
				myConn.AddInParameter("@commentEnabled", DbType.Boolean, post.IsCommentsEnabled);
				myConn.AddInParameter("@raters", DbType.Int32, post.Raters);
				myConn.AddInParameter("@rating", DbType.Single, post.Rating);
				myConn.AddInParameter("@slug", DbType.String, post.Slug);

				myConn.ExecuteNonQuery();

				//Update associated tables
				UpdateTags(post, myConn);
				UpdateCategories(post, myConn);
				UpdateComments(post, myConn);
				UpdateNotify(post, myConn);

				myConn.Commit();

				if (!Post.Items.Contains(post))
					Post.Items.Add(post);

				Post.Items.Sort();
			}
			finally
			{
				//if commit worked then this will do nothing
				myConn.Rollback();
			}
		}


		/// <summary>
		/// Deletes a post from the data store.
		/// </summary>
		public override void DeletePost(Post post)
		{
			if (post == null)
				throw new ArgumentNullException("post");

			SQLManagedConnection myConn = SQLManagedConnection.GetConnectionForTransaction(_DatabaseName);

			try
			{
				string sqlQuery = "DELETE FROM be_PostTag WHERE PostID = @id;" +
						   "DELETE FROM be_PostCategory WHERE PostID = @id;" +
						   "DELETE FROM be_PostNotify WHERE PostID = @id;" +
						   "DELETE FROM be_PostComment WHERE PostID = @id;" +
						   "DELETE FROM be_Posts WHERE PostID = @id;";

				myConn.AddInParameter("@id", DbType.Guid, post.Id);
				myConn.ExecuteNonQuery();
				myConn.Commit();

				if (Post.Items.Contains(post))
					Post.Items.Remove(post);

				Post.Items.Sort();
			}
			finally
			{
				//try to rollback and close connection - if commit occured this won't do anything      
				myConn.Rollback();
			}
		}

		/// <summary>
		/// Retrieves all posts from the data store
		/// </summary>
		/// <returns>List of Posts</returns>
		public override List<Post> FillPosts()
		{
			List<Post> posts = new List<Post>();

			//for each entry found, cause a data load to occur - done this way so the reader is shut first!
			foreach (Guid guid in this.GetGuids("SELECT PostID FROM be_Posts"))
				posts.Add(Post.Load(guid));

			posts.Sort();
			return posts;
		}

		private void UpdateTags(Post post, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PostTag WHERE PostID = @id");
			conn.AddInParameter("@id", DbType.Guid, post.Id);
			conn.ExecuteNonQuery();

			foreach (string tag in post.Tags)
			{
				conn.GetTextCommand("INSERT INTO be_PostTag (PostID, Tag) VALUES (@id, @tag)");
				conn.AddInParameter("@id", DbType.Guid, post.Id);
				conn.AddInParameter("@tag", DbType.String, tag);
				conn.ExecuteNonQuery();
			}
		}

		private void UpdateCategories(Post post, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PostCategory WHERE PostID = @id");
			conn.AddInParameter("@id", DbType.Guid, post.Id);
			conn.ExecuteNonQuery();

			foreach (Category cat in post.Categories)
			{
				conn.GetTextCommand("INSERT INTO be_PostCategory (PostID, CategoryID) VALUES (@id, @cat)");
				conn.AddInParameter("@id", DbType.Guid, post.Id);
				conn.AddInParameter("@cat", DbType.Guid, cat.Id);
				conn.ExecuteNonQuery();
			}
		}

		private void UpdateComments(Post post, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PostComment WHERE PostID = @id");
			conn.AddInParameter("@id", DbType.Guid, post.Id);
			conn.ExecuteNonQuery();

			foreach (Comment comment in post.Comments)
			{
				conn.GetTextCommand("INSERT INTO be_PostComment (PostCommentID, PostID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved) " +
								"VALUES (@postcommentid, @id, @date, @author, @email, @website, @comment, @country, @ip, @isapproved)");
				conn.AddInParameter("@id", DbType.Guid, post.Id);
				conn.AddInParameter("@postcommentid", DbType.Guid, comment.Id);
				conn.AddInParameter("@date", DbType.DateTime, comment.DateCreated);
				conn.AddInParameter("@author", DbType.String, comment.Author);
				conn.AddInParameter("@email", DbType.String, comment.Email);
				conn.AddInParameter("@website", DbType.String, comment.Website.ToString());
				conn.AddInParameter("@comment", DbType.String, comment.Content);
				conn.AddInParameter("@country", DbType.String, comment.Country);
				conn.AddInParameter("@ip", DbType.String, comment.IP);
				conn.AddInParameter("@isapproved", DbType.Boolean, comment.IsApproved);
				conn.ExecuteNonQuery();
			}
		}

		private void UpdateNotify(Post post, SQLManagedConnection conn)
		{
			conn.GetTextCommand("DELETE FROM be_PostNotify WHERE PostID = @id");
			conn.AddInParameter("@id", DbType.Guid, post.Id);
			conn.ExecuteNonQuery();

			foreach (string email in post.NotificationEmails)
			{
				conn.GetTextCommand("INSERT INTO be_PostNotify (PostID, NotifyAddress) VALUES (@id, @notify)");
				conn.AddInParameter("@id", DbType.Guid, post.Id);
				conn.AddInParameter("@notify", DbType.String, email);
				conn.ExecuteNonQuery();
			}
		}
	}
}
